package com.lcy.demo;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by 娄存银 on 2017/4/10.
 */
public class ExcelDemo {
    public static String PATH = "D:\\test.xls";
    public static void main(String[] args) {
        try {
            createWorkBook();
            readWorkBook(PATH);
        } catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
    }

    private static void createWorkBook() throws IOException {
        Workbook workbook = new HSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        Row titleRow = sheet.createRow(0);
        String[] titles = {"ID","姓名","年龄","日期"};
        for (int i = 0; i < titles.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
        }

        List<User> users = createUsers();
        for (int i = 0; i < users.size(); i++) {
            User user = users.get(i);
            Row row = sheet.createRow(i+1);
            Cell id = row.createCell(0);
            id.setCellValue(user.getId());
            Cell name = row.createCell(1);
            name.setCellValue(user.getName());
            Cell age = row.createCell(2);
            age.setCellValue(user.getAge());
            Cell date = row.createCell(3);
            date.setCellValue(user.getDate());
            CellStyle style = workbook.createCellStyle();
            style.setDataFormat(workbook.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
            date.setCellStyle(style);
        }

        File file = new File(PATH);
        FileOutputStream fos = new FileOutputStream(file);
        workbook.write(fos);
        workbook.close();
        fos.close();
    }

    public static void readWorkBook(String path) throws IOException, InvalidFormatException {
        File file = new File(path);
        Workbook workbook = WorkbookFactory.create(file);
        workbook.close();
        Sheet sheet = workbook.getSheetAt(0);
        for (Row row:sheet){
            int num = row.getRowNum();
            if(num == 0){
                for (Cell cell:row){
                    System.out.println("title:"+cell.getStringCellValue());
                }
            }else {
                User user = new User();
                Cell id = row.getCell(0);
                Cell name = row.getCell(1);
                Cell age = row.getCell(2);
                Cell date = row.getCell(3);
                user.setId((int) id.getNumericCellValue());
                user.setName(name.getStringCellValue());
                user.setAge((int) age.getNumericCellValue());
                user.setDate(date.getDateCellValue());
                System.out.println(user);
            }
        }
    }

    private static List<User> createUsers() {
        List<User> list = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            User user = new User();
            user.setId(i+1);
            user.setName("name_"+i);
            user.setAge(20+i);
            user.setDate(new Date());
            list.add(user);
        }
        return list;
    }

    public static class User{
        private int id;
        private String name;
        private int age;
        private Date date;

        public int getId() {
            return id;
        }

        public void setId(int id) {
            this.id = id;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public int getAge() {
            return age;
        }

        public void setAge(int age) {
            this.age = age;
        }

        public Date getDate() {
            return date;
        }

        public void setDate(Date date) {
            this.date = date;
        }

        @Override
        public String toString() {
            return "User{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", age=" + age +
                    ", date=" + date +
                    '}';
        }
    }
}
